﻿//<TunynetCopyright>
//--------------------------------------------------------------
//<copyright>青岛拓宇网络科技有限公司 2005-2008</copyright>
//<version>V0.5</verion>
//<createdate>3/20/2008 10:15:11 AM</createdate>
//<author>xier</author>
//<email>xier@tunynet.com</email>
//<log date="3/20/2008 10:15:11 AM">创建</log>
//--------------------------------------------------------------
//</TunynetCopyright>

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Tunynet.QingdaoCustoms.Common;
using Tunynet.QingdaoCustoms.Data.Utils;
using Tunynet.QingdaoCustoms.Serialization;
using ChinaCustoms.Qingdao.Workflow.Supports.DataProvider;

namespace ChinaCustoms.Qingdao.Workflow.Supports
{
    public class TemplateDataProvider
    {
        string databaseOwner = "dbo";

        private static TemplateDataProvider _instance = null;
        public static TemplateDataProvider Instance()
        {
            if (_instance == null)
                _instance = new TemplateDataProvider();

            return _instance;
        }


        #region WorkflowTemplate

        /// <summary>
        /// 创建或更新工作流模板
        /// </summary>
        public WorkflowTemplateInfo CreateUpdateWorkflowTemplate(WorkflowTemplateInfo workflowTemplateInfo, DataProviderAction action)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("wf_WorkflowTemplate_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = (int)action;

                if (action == DataProviderAction.Create)
                    myCommand.Parameters.Add("@WorkflowTemplateID", SqlDbType.Int).Direction = ParameterDirection.Output;
                else
                    myCommand.Parameters.Add("@WorkflowTemplateID", SqlDbType.Int).Value = workflowTemplateInfo.WorkflowTemplateID;

                myCommand.Parameters.Add("@WorkflowTemplateName", SqlDbType.NVarChar, 200).Value = workflowTemplateInfo.WorkflowTemplateName;
                myCommand.Parameters.Add("@ApplicationID", SqlDbType.UniqueIdentifier).Value = workflowTemplateInfo.ApplicationID;
                myCommand.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 200).Value = workflowTemplateInfo.ApplicationName;
                myCommand.Parameters.Add("@FunctionID", SqlDbType.UniqueIdentifier).Value = workflowTemplateInfo.FunctionID;
                myCommand.Parameters.Add("@FunctionName", SqlDbType.NVarChar, 200).Value = workflowTemplateInfo.FunctionName;
                myCommand.Parameters.Add("@CreateUserID", SqlDbType.UniqueIdentifier).Value = workflowTemplateInfo.CreateUserID;
                myCommand.Parameters.Add("@CreateUserName", SqlDbType.NVarChar, 200).Value = workflowTemplateInfo.CreateUserName;
                myCommand.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = workflowTemplateInfo.CreateDate;
                myCommand.Parameters.Add("@EntityXML", SqlDbType.NText).Value = workflowTemplateInfo.EntityXML;
                myCommand.Parameters.Add("@IsDelete", SqlDbType.Bit).Value = workflowTemplateInfo.IsDelete;
                myCommand.Parameters.Add("@IsPublic", SqlDbType.Bit).Value = workflowTemplateInfo.IsPublic;

                //SerializerData serializerData = workflowTemplateInfo.GetSerializerData();
                //myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = serializerData.Keys;
                //myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = serializerData.Values;

                myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = string.Empty;
                myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = string.Empty;

                myConnection.Open();
                myCommand.ExecuteNonQuery();

                if (action == DataProviderAction.Create)
                    workflowTemplateInfo.WorkflowTemplateID = Convert.ToInt32(myCommand.Parameters["@WorkflowTemplateID"].Value);

                myConnection.Close();

                return workflowTemplateInfo;
            }
        }


        /// <summary>
        /// 删除工作流模板
        /// </summary>
        public void DeleteWorkflowTemplate(int workflowTemplateID)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("wf_WorkflowTemplate_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = (int)DataProviderAction.Delete;
                myCommand.Parameters.Add("@WorkflowTemplateID", SqlDbType.Int).Value = workflowTemplateID;

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        /// <summary>
        /// 通过ApplictionID获取工作流模板
        /// </summary>
        public PagingDataSet<WorkflowTemplateInfo> GetWorkflowTemplateByApplictionID(Guid ApplictionID)
        {
            throw new System.NotImplementedException();
        }

        /// <summary>
        /// 获取工作流模板集合
        /// </summary>
        /// <param name="query">工作流模板查询实体信息</param>
        /// <returns></returns>
        public IList<WorkflowTemplateInfo> GetWorkflowTemplates(WorkflowTemplateQuery query)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".wf_WorkflowTemplate_GetSet", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                StringBuilder sqlPopulateSelect = new StringBuilder("SET Transaction Isolation Level Read UNCOMMITTED ");
                StringBuilder selectClause = new StringBuilder(" select ");

                if (query.IgnorePaging)
                    selectClause.AppendFormat(" Top {0} ", query.PageSize);
                else if (query.MaxRecords > 0)
                    selectClause.AppendFormat(" Top {0} ", query.MaxRecords);

                selectClause.Append(" T.WorkflowTemplateID ");

                StringBuilder fromClause = new StringBuilder();
                StringBuilder whereClause = new StringBuilder();

                fromClause.AppendFormat(" from {0}.wf_WorkflowTemplate T ", this.databaseOwner);
                whereClause.AppendFormat(" where 1 = 1 ");
                if (query.WorkflowTemplateID > 0)
                {
                    whereClause.AppendFormat(" and T.WorkflowTemplateID = {0} ", query.WorkflowTemplateID);
                }

                if (string.IsNullOrEmpty(query.WorkflowTemplateName) == false)
                {
                    whereClause.AppendFormat(" and T.WorkflowTemplateName like '%{0}%' ", query.WorkflowTemplateName);
                }

                if (query.IsPublic == true)
                {
                    whereClause.AppendFormat(" and T.IsPublic= {0} ", 1);
                }
                else
                {
                    whereClause.AppendFormat(" and T.IsPublic= {0} ", 0);
                }

                if (query.IsDelete == true)
                {
                    whereClause.AppendFormat(" and T.IsDelete = {0}", 1);
                }
                else
                {
                    whereClause.AppendFormat(" and T.IsDelete = {0}", 0);
                }

                if (query.FunctionID != Guid.Empty)
                {
                    whereClause.AppendFormat(" and T.FunctionID = '{0}' ", query.FunctionID);
                }

                if (string.IsNullOrEmpty(query.FunctionName) == false)
                {
                    whereClause.AppendFormat(" and T.FunctionName like '%{0}%' ", query.FunctionName);
                }

                if (query.CreateUserID != Guid.Empty)
                {
                    whereClause.AppendFormat(" and T.CreateUserID = '{0}' ", query.CreateUserID);
                }

                if (string.IsNullOrEmpty(query.CreateUserName) == false)
                {
                    whereClause.AppendFormat(" and T.CreateUserName like '%{0}%' ", query.CreateUserName);
                }


                //StringBuilder orderClause = new StringBuilder(" ORDER BY ");

                //switch (query.SortBy)
                //{
                //    case SortEventPostsBy.CreateDate:
                //        orderClause.Append(" P.CreateDate ");
                //        break;
                //    case SortEventPostsBy.Event:
                //        orderClause.Append(" P.EventID ");
                //        break;
                //    default:
                //        orderClause.Append(" P.CreateDate ");
                //        break;
                //}

                //if (query.SortOrder == SpaceBuilder.Components.SortOrder.Descending)
                //    orderClause.Append(" desc");

                sqlPopulateSelect.Append(selectClause.ToString());
                sqlPopulateSelect.Append(fromClause.ToString());
                sqlPopulateSelect.Append(whereClause.ToString());
                //sqlPopulateSelect.Append(orderClause.ToString());

                myCommand.Parameters.Add("@SqlPopulate", SqlDbType.NText).Value = sqlPopulateSelect.ToString();
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = query.PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = query.PageSize;
                myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;

                IList<WorkflowTemplateInfo> list= new List<WorkflowTemplateInfo>();

                myConnection.Open();
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        list.Add(PopulateWorkflowTemplateInfoFromIDataReader(dr));
                    }
                    dr.Close();
                }
                myCommand.Dispose();
                myConnection.Close();

                return list;
            }
        }

        /// <summary>
        /// 获取工作流模板
        /// </summary>
        public WorkflowTemplateInfo GetWorkflowTemplate(int workflowTemplateID)
        {
            WorkflowTemplateInfo info = null;
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("wf_WorkflowTemplate_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@WorkflowTemplateID", System.Data.SqlDbType.Int).Value = workflowTemplateID;

                myConnection.Open();
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleRow))
                {
                    if (dr.Read())
                    {
                        info = PopulateWorkflowTemplateInfoFromIDataReader(dr);
                    }
                }
                myConnection.Close();

                return info;
            }
        }

        #endregion
        /// <summary>
        /// 获取SQL数据库链接
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetSqlConnection()
        {
            try
            {
                //return new SqlConnection(SqlHelper.WorkflowConnectionString);
                return SqlHelper.WorkflowConnection;
            }
            catch
            {
                throw new Exception("SQL Connection String is invalid.");
            }
        }

        /// <summary>
        /// 通过IDataReader组装WorkflowTemplateInfo
        /// </summary>
        private WorkflowTemplateInfo PopulateWorkflowTemplateInfoFromIDataReader(IDataReader dr)
        {
            WorkflowTemplateInfo info = new WorkflowTemplateInfo();
            info.WorkflowTemplateID = Convert.ToInt32(dr["WorkflowTemplateID"]);
            info.WorkflowTemplateName = Convert.ToString(dr["WorkflowTemplateName"]);
            info.ApplicationID = new Guid(Convert.ToString(dr["ApplicationID"]));
            info.ApplicationName = Convert.ToString(dr["ApplicationName"]);
            info.FunctionID = new Guid(Convert.ToString(dr["FunctionID"]));
            info.FunctionName = Convert.ToString(dr["FunctionName"]);
            info.CreateUserID = new Guid(Convert.ToString(dr["CreateUserID"]));
            info.CreateUserName = Convert.ToString(dr["CreateUserName"]);
            info.CreateDate = Convert.ToDateTime(dr["CreateDate"]);
            info.EntityXML = Convert.ToString(dr["EntityXML"]);
            info.IsDelete = Convert.ToBoolean(dr["IsDelete"]);
            info.IsPublic = Convert.ToBoolean(dr["IsPublic"]);

            //info.SetSerializerData(DataProviderHelper.PopulateSerializerDataFromDataReader(dr));
            return info;
        }
    }
}
